﻿using ReflectionStudio.Controls;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Linq;
using System.Xml.Linq;
using WPFExcelReport;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC : BaseFunction
    {
        public string Company = "ITX";
        public DateTime tomorrow = DateTime.Now.AddDays(1);
        public DateTime yesterday = DateTime.Now.AddDays(-1);
        public string ITXMANDANT = "00", ITXUSER = "ITXVVVK ", ITXDATE = "20120723";
        public string today = "";
        public string Right(string str, int num)
        {
            try
            {
                int leng = str.Length;
                str = str.Substring(leng - num, num);
            }
            catch (Exception ex)
            {
            }
            return str;
        }
        public string Left(string str, int num)
        {
            try
            {
                int leng = str.Length;
                str = str + "                                                                                ";
                str = str.Substring(0, num);
            }
            catch (Exception ex)
            {

            }
            return str;
        }
        OracleCommand or1;
        public GB2312Convert8859P1 gb2312 = new GB2312Convert8859P1();
        public string ApplicationPath
        {
            get { return System.IO.Path.GetDirectoryName(System.Windows.Forms.Application.ExecutablePath); }
        }
        public INTEXDBC()
        {

        }
        public INTEXDBC(OracleConnection _OracleDB, IntexReptServBLClient _proxy)
        {
            OracleDB = _OracleDB;
            proxy = _proxy;
        }

        public INTEXDBC(OracleConnection _OracleDB, IntexReptServBLClient _proxy, string company)
        {
            OracleDB = _OracleDB;
            proxy = _proxy;
            Company = company;
        }
        public void GetStkWeight(DataRow rwb, ref double stkgrossw, ref double stkweight, ref Dictionary<string, string> makeup, ref  Dictionary<string, string> packup)
        {
            string AUFMSL = rwb["WBBAUFMSL"].ToString();
            string WBBAUFMSL = "0";
            double innerweight = 0;
            double outerweight = 0;
            if (AUFMSL.Trim().Length > 0)
            {
                AUFMSL = "00" + (AUFMSL + "                                     ").Substring(0, 21) + "V";
                if (makeup.ContainsKey(AUFMSL))
                {
                    WBBAUFMSL = makeup[AUFMSL];
                }
                else
                {
                    WBBAUFMSL = GetSDDAT("0114", AUFMSL).Substring(61, 8);
                    makeup.Add(AUFMSL, WBBAUFMSL);
                }
            }
            if (WBBAUFMSL.Trim() == "")
                innerweight = 0;
            else
            {
                innerweight = System.Convert.ToDouble(WBBAUFMSL) * 0.01;
            }
            string PACKTYP = rwb["WBBPACKTYP"].ToString();
            string WBBPACKTYP = "";
            if (PACKTYP.Trim().Length > 0)
            {
                PACKTYP = "00" + (PACKTYP + "                                     ").Substring(0, 21) + "V";
                if (packup.ContainsKey(PACKTYP))
                {
                    WBBPACKTYP = packup[PACKTYP];
                }
                else
                {
                    WBBPACKTYP = GetSDDAT("0258", PACKTYP).Substring(61, 8);
                    packup.Add(PACKTYP, WBBPACKTYP);
                }
            }
            if (WBBPACKTYP.Trim() == "")
                outerweight = 0;
            else
            {
                outerweight = System.Convert.ToDouble(WBBPACKTYP) * 0.01;
            }
            stkweight = System.Math.Round(0.001 * System.Convert.ToDouble(rwb["WBBIGEW"].ToString()), 1);
            stkgrossw = stkweight + innerweight + outerweight;
        }
        public void BlindMap(ref DataRow dwLocal, DataRow dwData)
        {
            foreach (DataColumn dc in dwLocal.Table.Columns)
            {
                string colum = dc.ColumnName;
                if (dwData.Table.Columns.Contains(colum))
                {
                    if (dc.DataType.ToString() == "System.Byte[]")
                    {
                        dwLocal[colum] = System.Text.Encoding.Default.GetBytes(dwData[colum].ToString());
                    }
                    else
                    {
                        if (dwData[colum].ToString() == "")
                        { }
                        else
                        {
                            dwLocal[colum] = dwData[colum];
                        }
                    }
                }
            }
        }
        public string InjectData(string text, DataRow dwData)
        {
            if (dwData == null)
                return "";
            foreach (DataColumn cm in dwData.Table.Columns)
            {
                string cn = cm.ColumnName;
                 
                if (cm.DataType.FullName.Contains("System.Byte[]"))
                {
                    if (text.Contains("cast_to_raw"))
                    {
                        text = text.Replace(":" + cn + ":", "'" + dwData[cn].ToString() + "'");
                        continue;
                    }
                    if (dwData[cn].ToString() =="")
                        continue;
                    byte[] b = (byte[])dwData[cn];
                    string value = "";
                    foreach (byte bt in b)
                    {
                        value += bt;
                    }
                    text = text.Replace(":" + cn + ":", "'" + value + "'");
                    continue;
                }
                //.Replace("'","\"")
                text = text.Replace(":" + cn + ":", "'" + dwData[cn].ToString() + "'");

                if (!text.Contains(":"))
                    break;
            }
            return text;
        }
        private string GetDDMMYY(string YYYYMMDD)
        {
            //VKPLIEFTERM,VKPVERZTERM1,VKPVERZTERM2,VKPVERZTERM3,VKPVERZTERM4,VKPVERZTERM5对应的格式为YYYYMMDD
            string date = "";
            if (YYYYMMDD.Trim().Length < 8)
                return "        ";
            date = YYYYMMDD.Substring(6, 2) + "." + YYYYMMDD.Substring(4, 2) + "." + YYYYMMDD.Substring(2, 2);
            return date;
        }
        public string GetSeq()
        {
            string result = "0";
            OracleCommand or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT  SEQ_ICC_ITX.NEXTVAL AS SEQ FROM DUAL";
            DataSet SResult = Connect2Proxy(or1);
            foreach (DataRow row in SResult.Tables[0].Rows)
            {
                result = row["SEQ"].ToString();
            }
            return result;
        }
        public string GetICCSeq()
        {
            string result = "0";
            OracleCommand or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT  ICC_GLLG_SEQ.NEXTVAL AS SEQ FROM DUAL";
            DataSet SResult = Connect2Proxy(or1);
            foreach (DataRow row in SResult.Tables[0].Rows)
            {
                result = row["SEQ"].ToString();
            }
            return result;
        }
        public void GetDBDATETIME(ref string DD,ref string TT)
        {
            
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "select to_char(sysdate,'yyyymmdd') as DD,to_char(sysdate,'hh24missss') as TT  from dual";
            DataSet SResult = Connect2Proxy(or1);
            foreach (DataRow row in SResult.Tables[0].Rows)
            {
                DD = row["DD"].ToString();
                TT = row["TT"].ToString();
            } 
        }
        public string GetDBDATE()
        {
            string result = "0";
            OracleCommand or1 = OracleDB.CreateCommand();
            or1.CommandText = "select to_char(sysdate,'yyyymmdd') as DD from dual";
            DataSet SResult = Connect2Proxy(or1);
            foreach (DataRow row in SResult.Tables[0].Rows)
            {
                result = row["DD"].ToString();
            }
            return result;
        }

        public string GetDBTIME()
        {
            string result = "0";
            OracleCommand or1 = OracleDB.CreateCommand();
            or1.CommandText = "select to_char(sysdate,'hh24missss') as TT from dual";
            DataSet SResult = Connect2Proxy(or1);
            foreach (DataRow row in SResult.Tables[0].Rows)
            {
                result = row["TT"].ToString();
            }
            return result;
        }
        public void StartTransation()
        {
            OracleCommand or1 = OracleDB.CreateCommand();
            Connect2Proxy(or1, "StartTransation");
        }
        public void RollBack()
        {
            OracleCommand or1 = OracleDB.CreateCommand();
            Connect2Proxy(or1, "RollBack");
        }
        public void Commit()
        {
            OracleCommand or1 = OracleDB.CreateCommand();
            Connect2Proxy(or1, "Commit");
        }
        public void MapData(bool IsConvert, ref DataRow dwReturn, DataRow dwInput)
        {
            if (dwInput == null)
                return;
            if (!IsConvert)
                return;

            foreach (DataColumn cm in dwInput.Table.Columns)
            {
                if (dwInput[cm.ColumnName].ToString() == "")
                    continue;
                if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                {
                    dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                }
            }
        }
        #region====MintexDB===========
        public void CompressMainkey(DataTable tb)
        {
            if (!tb.Columns.Contains("MAINKEY"))
                return;
            foreach (DataRow row in tb.Rows)
            {
                row["MAINKEY"] = DataToXml.CompressLongString(row["MAINKEY"].ToString());
            }
        }
        public XDocument QueryGPBST(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "GPBST";
            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT ROWNUM AS MAINKEY,GPBMANDANT,GPBPARTIE,GPBART,GPBVAR,GPBFARBE,GPBBSTKGG,GPBART AS PHOTO FROM GPBST WHERE GPBMANDANT='00'";
                if (option == "EQ")
                {
                    or1.CommandText += " AND (GPBNEUDAT>=" + today + " OR GPBAENDDAT>=" + today + ")";
                }
                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                keylist.Add("GPBMANDANT");
                keylist.Add("GPBPARTIE");
                keylist.Add("GPBART");
                keylist.Add("GPBVAR");
                keylist.Add("GPBFARBE");
                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);
                Return.Merge(Result);
            }
            catch (Exception ex) { }
            return doc;
        }
        public XDocument QueryWBDAT(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "WBDAT";
            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT  ROWNUM AS MAINKEY, WBMANDANT,WBWAKZ,WBART,WBVAR,WBCOLORIT,WBFARBE,WBBESCHICH,"
                + " WBZUSTAND,WBSTK,WBMTR FROM WBDAT WHERE WBMANDANT='00'";
                if (dwData != null)
                {
                    or1.CommandText = "SELECT  ROWNUM AS MAINKEY, WBMANDANT,WBWAKZ,WBART,WBVAR,WBCOLORIT,WBFARBE,WBBESCHICH,"
                        + " WBZUSTAND,WBSTK,WBMTR,WBART AS PHOTO FROM WBDAT WB RIGHT JOIN WASTAMM ON WBMANDANT=WAMANDANT AND WBART=WAART AND WBVAR=WAVAR"
                        + " WHERE WAMANDANT=:WAMANDANT AND WAKUNDNR='00000000' AND WADART=:WADART AND WADVAR=:WADVAR ";

                    or1.Parameters.Add("WAMANDANT", OracleType.NVarChar).Value = dwData["VAAMANDANT"].ToString();
                    or1.Parameters.Add("WADART", OracleType.NVarChar).Value = dwData["VAADART"].ToString();
                    or1.Parameters.Add("WADVAR", OracleType.NVarChar).Value = dwData["VAADVAR"].ToString();
                }
                if (option == "EQ")
                {
                    or1.CommandText += " AND (WBAENDDAT>=" + today + " OR WBNEUDAT>=" + today + ")";
                }
                //WBAENDDAT,WBNEUDAT
                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                keylist.Add("WBMANDANT");
                keylist.Add("WBWAKZ");
                keylist.Add("WBART");
                keylist.Add("WBVAR");
                keylist.Add("WBCOLORIT");
                keylist.Add("WBFARBE");
                keylist.Add("WBBESCHICH");
                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);
                Return.Merge(Result);

            }
            catch (Exception ex) { }
            return doc;
        }
        public void InsertAnyTable(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            DataTable tb = dwData.Table;
            string insert = "INSERT INTO " + tb.TableName + " (";
            string values = " VALUES (";
            string commandtext = "";
            foreach (DataColumn field in dwData.Table.Columns)
            {
                string name = field.ColumnName;
                if (name == "ROWID")
                    name = "ROWNID";
                insert += name + ",";
                values += ":" + name + ",";
                if (name == "ROWNID")
                {
                    or1.Parameters.Add(name, OracleType.NVarChar).Value = dwData["ROWID"];
                }
                else
                {
                    or1.Parameters.Add(name, OracleType.NVarChar).Value = ConvertGB2312To8859P1(dwData[name].ToString());
                }
            }
            insert += "***";
            values += "***";
            commandtext = insert.Replace(",***", ")") + values.Replace(",***", ")");
            or1.CommandText = commandtext;
            Connect2Proxy(or1);
        }

        public XDocument QueryVAFARBEN(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "VAFARBEN";
            try
            {
                int ip = 0;
                List<string> Photos = new List<string>();
                //Photos.Add("P7");
                Photos.Add("P3");
                Photos.Add("P4");
                Photos.Add("P5");
                Photos.Add("P6");
                Photos.Add("P2");
                Photos.Add("P1");
                //Photos.Add("P8");
                //Photos.Add("P9");
                Photos.Add("P10");
                //Photos.Add("P11");
                or1 = OracleDB.CreateCommand();
                if (dwData == null)
                {
                    or1.CommandText = "SELECT ROWNUM AS MAINKEY, "
                    + "VAFMANDANT,VAFKOLL,VAFART,VAFFARBE,VAFBEZ,VAFFFARBE,VAFCOLORIT,VAFBESCHICH, "
                    + "Trim(VAFART)||Trim(VAFFARBE)||'.jpg'  AS PHOTO"
                    + " FROM VAFARBEN WHERE VAFMANDANT='00'";
                }
                else
                {
                    or1.CommandText = "SELECT ROWNUM AS MAINKEY, "
                        + "VAFMANDANT,VAFKOLL,VAFART,VAFFARBE,VAFBEZ,VAFFFARBE,VAFCOLORIT,VAFBESCHICH, "
                        + "Trim(VAFART)||Trim(VAFFARBE)  AS PHOTO"
                        + " FROM VAFARBEN  "
                        + " WHERE VAFMANDANT=:VAFMANDANT AND VAFKOLL=:VAFKOLL AND VAFART=:VAFART";
                    or1.Parameters.Add("VAFMANDANT", OracleType.NVarChar).Value = dwData["VAAMANDANT"].ToString();
                    or1.Parameters.Add("VAFKOLL", OracleType.NVarChar).Value = dwData["VAAKOLL"].ToString();
                    or1.Parameters.Add("VAFART", OracleType.NVarChar).Value = dwData["VAAART"].ToString();
                }
                if (option == "EQ")
                {
                    or1.CommandText += " AND (VAFAENDDAT>=" + today + " OR VAFNEUDAT>=" + today + ")";
                }
                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    foreach (DataRow vf in Result.Tables[0].Rows)
                    {
                        vf["VAFBEZ"] = gb2312.Convert(vf["VAFBEZ"].ToString(), null, null, null);
                        vf["PHOTO"] = Photos[ip];
                        ip++;
                        if (ip > Photos.Count() - 1)
                        {
                            ip = 0;
                        }
                    }
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                keylist.Add("VAFMANDANT");
                keylist.Add("VAFKOLL");
                keylist.Add("VAFART");
                keylist.Add("VAFFARBE");
                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);
                Return.Merge(Result);
            }
            catch (Exception ex) { }
            return doc;
        }

        public XDocument QueryVAKOLL(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            OracleCommand or1;
            string table = "VAKOLL";
            try
            {

                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT DISTINCT ROWNUM AS MAINKEY"
                + ",VAKMANDANT,VAKKOLL,VAKBEZ,VAKKOLL AS PHOTO"
                + " FROM VAKOLL "
                + " WHERE VAKMANDANT='00'";
                if (option == "EQ")
                {
                    or1.CommandText += " AND (VAKAENDDAT>=" + today + " OR VAKNEUDAT>=" + today + ")";
                }
                //VAAAENDDAT,VAANEUDAT
                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    foreach (DataRow vf in Result.Tables[0].Rows)
                    {
                        vf["VAKBEZ"] = gb2312.Convert(vf["VAKBEZ"].ToString(), null, null, null);
                    }
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                keylist.Add("VAKMANDANT");
                keylist.Add("VAKKOLL");
                keylist.Add("VAKBEZ");
                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
            }
            return doc;
        }
        public XDocument QueryVAART(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "VAART";
            try
            {
                int ip = 0;
                List<string> Photos = new List<string>();
                //Photos.Add("P7");
                Photos.Add("P3");
                Photos.Add("P4");
                Photos.Add("P5");
                Photos.Add("P6");
                Photos.Add("P2");
                Photos.Add("P1");
                //Photos.Add("P8");
                //Photos.Add("P9");
                Photos.Add("P10");
                //Photos.Add("P11");
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT DISTINCT ROWNUM AS MAINKEY"
                + ",VAAMANDANT,VAAKOLL,VAAART,VAAARTST,VAABEZ,VAABRTVON,VAAMEBRT,VAADART,VAADVAR,VAAART AS PHOTO"
                + " FROM VAART "
                + " WHERE VAAMANDANT='00'";
                if (option == "EQ")
                {
                    or1.CommandText += " AND (VAAAENDDAT>=" + today + " OR VAANEUDAT>=" + today + ")";
                }
                //VAAAENDDAT,VAANEUDAT
                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    foreach (DataRow vf in Result.Tables[0].Rows)
                    {
                        vf["VAABEZ"] = gb2312.Convert(vf["VAABEZ"].ToString(), null, null, null);

                        vf["PHOTO"] = Photos[ip];
                        ip++;
                        if (ip > Photos.Count() - 1)
                        {
                            ip = 0;
                        }
                    }
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                keylist.Add("VAAMANDANT");
                keylist.Add("VAAKOLL");
                keylist.Add("VAAART");
                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
            }
            return doc;
        }
        public XDocument QueryKDSTAMM(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "KDSTAMM";
            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT  ROWNUM AS MAINKEY,KDMANDANT,KDKUNDNR,KDKUNDNA,KDABCKL,KDNAME1,"
                    + "KDKNAME,KDNAME2,KDSTRASSE1,KDSTRASSE2,KDTEL,KDMAILADR, KDKUNDNA AS PHOTO FROM KDSTAMM WHERE KDMANDANT='00'";
                if (option == "EQ")
                {
                    or1.CommandText += " AND (KDAEND>=" + today + " OR KDNEUD>=" + today + ")";
                }
                //KDAEND,KDNEUD
                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    foreach (DataRow vf in Result.Tables[0].Rows)
                    {
                        vf["KDKUNDNA"] = gb2312.Convert(vf["KDKUNDNA"].ToString(), null, null, null);
                        vf["KDNAME1"] = gb2312.Convert(vf["KDNAME1"].ToString(), null, null, null);
                        vf["KDKNAME"] = gb2312.Convert(vf["KDKNAME"].ToString(), null, null, null);
                        vf["KDNAME2"] = gb2312.Convert(vf["KDNAME2"].ToString(), null, null, null);
                        vf["KDSTRASSE1"] = gb2312.Convert(vf["KDSTRASSE1"].ToString(), null, null, null);
                        vf["KDSTRASSE2"] = gb2312.Convert(vf["KDSTRASSE2"].ToString(), null, null, null);
                    }
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                keylist.Add("KDMANDANT");
                keylist.Add("KDKUNDNR");
                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);

                Return.Merge(Result);
            }
            catch (Exception ex)
            {
            }
            return doc;
        }
        public XDocument QueryVKPOS(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "VKPOS";
            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT  ROWNUM AS MAINKEY,VKPMANDANT,VKPAUFKRS, VKPAUFLFD,VKPAUFPOS," +
                        "VKPNEUDAT,VKPKUNDNR"
                        + ",VKPKOLL,VKPART,VKPFARBE,VKPMENGE1,VKPMENGE2,VKPRESERV,VKPZUGET,VKPFREIGE,VKPGAUFBEST,VKPKOMMISS"
                        + ",VKPFAKT,VKPVERS,VKPEINGET,VKPOFFEN,VKPMEEH"
                        + ", VKPLIEFTERM,VKPART AS PHOTO"
                        + " FROM VKPOS WHERE VKPMANDANT='00' AND VKPSA<>'AJ' ";
                if (dwData != null)
                {
                    or1.CommandText = "SELECT  ROWNUM AS MAINKEY,VKPMANDANT,VKPAUFKRS, VKPAUFLFD,VKPAUFPOS," +
                        "VKPNEUDAT,VKPKUNDNR"
                        + ",VKPKOLL,VKPART,VKPFARBE,VKPMENGE1,VKPMENGE2,VKPRESERV,VKPZUGET,VKPFREIGE,VKPGAUFBEST,VKPKOMMISS"
                        + ",VKPFAKT,VKPVERS,VKPEINGET,VKPOFFEN,VKPMEEH"
                        + ", VKPLIEFTERM"
                        + " FROM VKPOS WHERE  VKPSA<>'AJ'  AND VKPMANDANT=:VKPMANDANT AND VKPKOLL=:VKPKOLL AND VKPART=:VKPART";
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VAAMANDANT"].ToString();
                    or1.Parameters.Add("VKPKOLL", OracleType.NVarChar).Value = dwData["VAAKOLL"].ToString();
                    or1.Parameters.Add("VKPART", OracleType.NVarChar).Value = dwData["VAAART"].ToString();
                }

                if (option == "EQ")
                {
                    or1.CommandText += " AND (VKPNEUDAT>=" + today + " OR VKPPAENDDAT>=" + today + ")";
                }
                //VKPNEUDAT,VKPPAENDDAT
                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                keylist.Add("VKPMANDANT");
                keylist.Add("VKPAUFKRS");
                keylist.Add("VKPAUFLFD");
                keylist.Add("VKPAUFPOS");
                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);

                Return.Merge(Result);
            }
            catch (Exception ex)
            {
            }
            return doc;
        }
        public XDocument QueryVKKOPF(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "VKKOPF";
            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT ROWNUM AS MAINKEY,VKKMANDANT,VKKAUFKRS,VKKAUFLFD,VKKNEUDAT,VKKKUNDNR,VKKSACHBE "
                    + "from VKKOPF WHERE   VKKMANDANT='00' ";
                if (dwData != null)
                {
                    or1.CommandText = "SELECT ROWNUM AS MAINKEY,VKKMANDANT,VKKAUFKRS,VKKAUFLFD,VKKNEUDAT,VKKKUNDNR,VKKSACHBE,VKKKUNDNR AS PHOTO "
                        + "FROM VKKOPF VK RIGHT JOIN VKPOS  ON VKKMANDANT=VKPMANDANT AND VKKAUFKRS=VKPAUFKRS AND VKKAUFLFD=VKPAUFLFD "
                    + " WHERE   VKPSA<>'AJ'  AND VKPMANDANT=:VKPMANDANT AND VKPKOLL=:VKPKOLL AND VKPART=:VKPART";
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VAAMANDANT"].ToString();
                    or1.Parameters.Add("VKPKOLL", OracleType.NVarChar).Value = dwData["VAAKOLL"].ToString();
                    or1.Parameters.Add("VKPART", OracleType.NVarChar).Value = dwData["VAAART"].ToString();
                }
                if (option == "EQ")
                {
                    //VKKAENDDAT,VKKNEUDAT
                    or1.CommandText += " AND (VKKAENDDAT>=" + today + " OR VKKNEUDAT>=" + today + ")";
                }
                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                keylist.Add("VKKMANDANT");
                keylist.Add("VKKAUFKRS");
                keylist.Add("VKKAUFLFD");
                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);

                Return.Merge(Result);
            }
            catch (Exception ex)
            {
            }
            return doc;
        }
        public XDocument QueryWASTAMM(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "WASTAMM";
            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT  ROWNUM AS MAINKEY,WAMANDANT,WAART,WAVAR,WANEUB,WANEUD,WABEZ,WAFDDIM,WARWBRT,"
                + "WARGRQM,WARFDDIK,WARFDDIS,WABIND,WAFGRLFD,WAFFDDIK,WAFFDDIS,WADART,WADVAR " +
                    " FROM WASTAMM WHERE WAMANDANT='00' AND WAKUNDNR='        ' ";
                if (dwData != null)
                {

                    or1.CommandText = "SELECT  ROWNUM AS MAINKEY,WAMANDANT,WAART,WAVAR,WANEUB,WANEUD,WABEZ,WAFDDIM, WARGRQM"
                    + ",WAFFDDIK,WAFFDDIS,WABIND,WAFGRLFD ,"
                    + "WAFBRTL,WAFGRQM,WAFBEM,WAFILLER2,WADART,WADVAR,WAART AS PHOTO " +
                        " FROM WASTAMM "
                    + " WHERE WAMANDANT=:WAMANDANT AND WAKUNDNR='00000000' AND WADART=:WADART AND WADVAR=:WADVAR ";
                    or1.Parameters.Add("WAMANDANT", OracleType.NVarChar).Value = dwData["VAAMANDANT"].ToString();
                    or1.Parameters.Add("WADART", OracleType.NVarChar).Value = dwData["VAADART"].ToString();
                    or1.Parameters.Add("WADVAR", OracleType.NVarChar).Value = dwData["VAADVAR"].ToString();
                }

                if (option == "EQ")
                {
                    // WAAEND,WANEUD 
                    or1.CommandText += " AND (WAAEND>=" + today + " OR WANEUD>=" + today + ")";
                }

                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    foreach (DataRow vf in Result.Tables[0].Rows)
                    {
                        vf["WABEZ"] = gb2312.Convert(vf["WABEZ"].ToString(), null, null, null);
                    }
                    Result.Tables[0].TableName = table;
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                keylist.Add("WAMANDANT");
                keylist.Add("WAART");
                keylist.Add("WAVAR");
                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);

                Return.Merge(Result);
            }
            catch (Exception ex)
            {
            }
            return doc;
        }
        public XDocument QueryWAFARBEN(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "WAFARBEN";
            try
            {
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT ROWNUM AS MAINKEY,WAFMANDANT"
                + ",WAFART,WAFVAR,WAFZUART,WAFZUSATZ,WAFBEZ FROM WAFARBEN WHERE WAFMANDANT='00'";
                if (dwData != null)
                {
                    or1.CommandText = "SELECT ROWNUM AS MAINKEY,WAFMANDANT"
                        + ",WAFART,WAFVAR,WAFZUART,WAFZUSATZ,WAFBEZ,WAFART AS PHOTO FROM WAFARBEN WF RIGHT JOIN WASTAMM ON WAFART=WAART AND WAFVAR=WAVAR"
                        + " WHERE WAMANDANT=:WAMANDANT AND WAKUNDNR='00000000' AND WADART=:WADART AND WADVAR=:WADVAR ";
                    or1.Parameters.Add("WAMANDANT", OracleType.NVarChar).Value = dwData["VAAMANDANT"].ToString();
                    or1.Parameters.Add("WADART", OracleType.NVarChar).Value = dwData["VAADART"].ToString();
                    or1.Parameters.Add("WADVAR", OracleType.NVarChar).Value = dwData["VAADVAR"].ToString();
                }
                if (option == "EQ")
                {
                    // WAFAENDDAT,WAFNEUDAT
                    or1.CommandText += " AND (WAFAENDDAT>=" + today + " OR WAFNEUDAT>=" + today + ")";
                }
                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    foreach (DataRow vf in Result.Tables[0].Rows)
                    {
                        vf["WAFBEZ"] = gb2312.Convert(vf["WAFBEZ"].ToString(), null, null, null);
                    }

                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                keylist.Add("WAFMANDANT");
                keylist.Add("WAFART");
                keylist.Add("WAFVAR");
                keylist.Add("WAFZUART");
                keylist.Add("WAFZUSATZ");
                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
            }
            return doc;
        }
        public XDocument QueryWDECK(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "WDECK";
            try
            {
                //SELECT WDSA,WDAUFKRS,WDAUFLFD,WDAUFPOS,WDTYP,WDUPOS,WDWAKZ,WDART,WDDESS,WDCOLORIT,WDFARBE,WDBESCHICH,WDVAR,WDDAT,WDPRART,WDPRVAR FROM WDECK WHERE WDMANDANT='00'
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT  ROWNUM AS MAINKEY,WDMANDANT,WDSA,WDAUFKRS,WDAUFLFD,WDAUFPOS,WDTYP,WDUPOS,"
                + "WDWAKZ,WDART,WDDESS,WDCOLORIT,WDFARBE,WDBESCHICH,WDVAR,WDDAT,WDPRART,WDPRVAR,WDART AS PHOTO"
                + " FROM WDECK WHERE WDMANDANT='00'";

                if (option == "EQ")
                {
                    // WAFAENDDAT,WAFNEUDAT
                    //or1.CommandText += " AND (WAFAENDDAT>=" + today + " OR WAFNEUDAT>=" + today + ")";
                }
                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                keylist.Add("WDMANDANT");
                keylist.Add("WDSA");
                keylist.Add("WDAUFKRS");
                keylist.Add("WDAUFLFD");
                keylist.Add("WDAUFPOS");
                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);

                Return.Merge(Result);
            }
            catch (Exception ex)
            {
            }
            return doc;
        }
        public XDocument QueryABAUFM(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "ABAUFM";
            try
            {
                //SELECT ABAAUFTYP,ABAAUFKRS,ABAAUFLFD,ABAAUFPOS,ABAAPOS,ABAVKAUFTYP,ABAVKAUFKRS,ABAVKAUFLFD,ABAVKAUFPOS,ABAVKUPOS,ABAKUNDNR FROM ABAUFM WHERE ABAMANDANT='00'
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT  ROWNUM AS MAINKEY,"
                + "ABAMANDANT,ABAAUFTYP,ABAAUFKRS,ABAAUFLFD,ABAAUFPOS,ABAAPOS,ABAVKAUFTYP," +
                    "ABAVKAUFKRS,ABAVKAUFLFD,ABAVKAUFPOS,ABAVKUPOS,ABAKUNDNR,ABPAUFKRS AS PHOTO"
                    + " FROM ABAUFM WHERE ABAMANDANT='00'";

                if (option == "EQ")
                {
                    // ABAAENDDAT,ABANEUDAT
                    or1.CommandText += " AND (ABAAENDDAT>=" + today + " OR ABANEUDAT>=" + today + ")";
                }
                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                keylist.Add("ABAMANDANT");
                keylist.Add("ABAAUFTYP");
                keylist.Add("ABAAUFKRS");
                keylist.Add("ABAAUFLFD");
                keylist.Add("ABAAUFPOS");
                keylist.Add("ABAAPOS");
                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
            }
            return doc;
        }
        public XDocument QueryABPOS(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "ABPOS";
            try
            {
                //SELECT ABAAUFTYP,ABAAUFKRS,ABAAUFLFD,ABAAUFPOS,ABAAPOS,ABAVKAUFTYP,ABAVKAUFKRS,ABAVKAUFLFD,ABAVKAUFPOS,ABAVKUPOS,ABAKUNDNR FROM ABAUFM WHERE ABAMANDANT='00'
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT  ROWNUM AS MAINKEY,ABPMANDANT,ABPAUFKRS,ABPAUFLFD,ABPAUFPOS,ABPWAKZ,ABPART,ABPVAR,ABPFARBE,ABPSOLLMENGE1,ABPSOLLMENGE2,ABPSOLLMENGE3,ABPSOLLMENGE4,ABPSOLLMENGE5,ABPSOLLMENGE6,ABPSOLLMENGE7,ABPSOLLMENGE8," +
                    "ABPLIEFDAT,ABPSTARTDAT, ABPNEUDAT FROM ABPOS WHERE ABPMANDANT='00'";
                if (dwData != null)
                {
                    or1.CommandText = "SELECT  ROWNUM AS MAINKEY,ABPMANDANT,ABPAUFKRS,ABPAUFLFD,ABPAUFPOS,ABPWAKZ,ABPART,ABPVAR,ABPFARBE,ABPSOLLMENGE1,ABPSOLLMENGE2,ABPSOLLMENGE3,ABPSOLLMENGE4,ABPSOLLMENGE5,ABPSOLLMENGE6,ABPSOLLMENGE7,ABPSOLLMENGE8,"
                       + "ABPLIEFDAT,ABPSTARTDAT, ABPNEUDAT,ABPART AS PHOTO FROM ABPOS AB RIGHT JOIN WASTAMM ON ABPMANDANT=WAMANDANT AND ABPART=WAART"
                      + " WHERE WAMANDANT=:WAMANDANT AND WAKUNDNR='00000000' AND WADART=:WADART AND WADVAR=:WADVAR ";

                    or1.Parameters.Add("WAMANDANT", OracleType.NVarChar).Value = dwData["VAAMANDANT"].ToString();
                    or1.Parameters.Add("WADART", OracleType.NVarChar).Value = dwData["VAADART"].ToString();
                    or1.Parameters.Add("WADVAR", OracleType.NVarChar).Value = dwData["VAADVAR"].ToString();

                }
                if (option == "EQ")
                {
                    // ABPAENDDAT,ABPNEUDAT
                    or1.CommandText += " AND (ABPAENDDAT>=" + today + " OR ABPNEUDAT>=" + today + ")";
                }
                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                keylist.Add("ABPMANDANT");
                keylist.Add("ABPAUFKRS");
                keylist.Add("ABPAUFLFD");
                keylist.Add("ABPAUFPOS");
                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);

                Return.Merge(Result);
            }
            catch (Exception ex)
            {
            }
            return doc;
        }
        public XDocument QueryABFOLGE(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "ABFOLGE";
            try
            {
                //SELECT ABFAUFKRS,ABFAUFLFD,ABFAUFPOS,ABFAFOLGE,ABFAGANG,ABFBZAGANG,ABFSOLLSTART,ABFSOLLDAT FROM ABFOLGE WHERE ABFMANDANT='00'
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT  ROWNUM AS MAINKEY,ABFMANDANT,ABFAUFKRS,ABFAUFLFD,ABFAUFPOS,"
                + "  ABFAFOLGE,ABFAGANG,ABFBZAGANG,ABFSOLLSTART,ABFSOLLDAT "
                    + ",ABFAUFKRS AS PHOTO FROM ABFOLGE WHERE ABFMANDANT='00'";
                if (option == "EQ")
                {
                    // ABPAENDDAT,ABPNEUDAT
                    or1.CommandText += " AND (ABFAENDDAT>=" + today + " OR ABFNEUDAT>=" + today + ")";
                }
                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                keylist.Add("ABFMANDANT");
                keylist.Add("ABFAUFKRS");
                keylist.Add("ABFAUFLFD");
                keylist.Add("ABFAUFPOS");
                keylist.Add("ABFAFOLGE");

                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);

                Return.Merge(Result);
            }
            catch (Exception ex)
            {
            }
            return doc;
        }
        public XDocument QueryBAAUF(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "BAAUF";
            try
            {
                //SELECT BAAUFTYP,BAAUFKRS,BAAUFLFD,BAAUFPOS,BASA,BAPOS,BAAFOLGE,BADATVON,BAKART,BAKVAR,BAKFARBE
                //,BAMTR1,BAANZ1,BAMTR2,BAANZ2,BAMTR3,BAANZ3,BAMTR4,BAANZ4,BAMTR5,BAANZ5,BAMTR6,BAANZ6 ,BAMTR7,BAANZ7 FROM BAAUF WHERE BAMANDANT='00'
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT ROWNUM AS MAINKEY,BAMANDANT,BAAUFTYP,BAAUFKRS,BAAUFLFD,BAAUFPOS,BASA,BAPOS,BAAFOLGE,BADATVON,BAKART,BAKVAR,BAKFARBE"
                    + ",BAMTR1,BAANZ1,BAMTR2,BAANZ2,BAMTR3,BAANZ3,BAMTR4,BAANZ4,BAMTR5,BAANZ5,BAMTR6,BAANZ6 ,BAMTR7,BAANZ7 " +
                    " ,BAKART AS AS PHOTO FROM BAAUF WHERE BAMANDANT='00'";

                if (option == "EQ")
                {
                    or1.CommandText += " AND (BAAENDDAT>=" + today + " OR BANEUDAT>=" + today + ")";
                }
                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                keylist.Add("BAMANDANT");
                keylist.Add("BAAUFKRS");
                keylist.Add("BAAUFLFD");
                keylist.Add("BAAUFPOS");
                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
            }
            return doc;
        }
        public XDocument QueryGAAUF(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "GAAUF";
            try
            {
                //SELECT GAAUFKRS,GAAUFLFD,GAAUFPOS,GASA,GAPOS,GAART,GAVAR,GAFARBE,GAKG1,GAKG2,GAKG3,GAKG4,GAKG5,GAKG6,GAKG7 FROM GAAUF WHERE GAMANDANT='00'

                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT  ROWNUM AS MAINKEY,GAMANDANT,GAAUFKRS,GAAUFLFD,GAAUFPOS,GASA,GAPOS,GAART,GAVAR,GAFARBE"
                + ",GAKG1,GAKG2,GAKG3,GAKG4,GAKG5,GAKG6,GAKG7,GAART AS PHOTO"
                    + "  FROM GAAUF WHERE GAMANDANT='00'";

                if (option == "EQ")
                {
                    or1.CommandText += " AND (GAAENDDAT>=" + today + " OR GANEUDAT>=" + today + ")";
                }

                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                // GAMANDANT,GAAUFKRS,GAAUFLFD,GAAUFPOS,GASA,GAPOS
                keylist.Add("GAMANDANT");
                keylist.Add("GAAUFKRS");
                keylist.Add("GAAUFLFD");
                keylist.Add("GAAUFPOS");
                keylist.Add("GASA");
                keylist.Add("GAPOS");
                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
            }
            return doc;
        }
        public XDocument QueryGSDAT(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "GSDAT";
            try
            {
                //SELECT GSART,GSVAR,GSHANDLNR,GSGARNBEZ,GSNUMART,GSGARNNR,GSFACH,GSRECHNUM,GSRECHNR FROM GSDAT WHERE GSMANDANT='00'
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT   ROWNUM AS MAINKEY, GSMANDANT,GSART,GSVAR,GSHANDLNR,GSGARNBEZ,GSNUMART"
                + ",GSGARNNR,GSFACH,GSRECHNUM,GSRECHNR,GSART AS PHOTO FROM GSDAT WHERE GSMANDANT='00'";

                if (option == "EQ")
                {
                    or1.CommandText += " AND (GSAENDDAT>=" + today + " OR GSNEUDAT>=" + today + ")";
                }
                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                // GAMANDANT,GAAUFKRS,GAAUFLFD,GAAUFPOS,GASA,GAPOS
                keylist.Add("GSMANDANT");
                keylist.Add("GSART");
                keylist.Add("GSVAR");
                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);

                Return.Merge(Result);
            }
            catch (Exception ex)
            {
            }
            return doc;
        }
        public XDocument QueryGSCOLOR(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "GSCOLOR";
            try
            {
                //SELECT GSCART,GSCVAR,GSCFARBE, GSCBEZ FROM GSCOLOR WHERE GSCMANDANT='00'
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT ROWNUM AS MAINKEY,GSCMANDANT,GSCART,GSCVAR,GSCFARBE, GSCBEZ "
                + ",GSCART AS PHOTO FROM GSCOLOR WHERE GSCMANDANT='00'";
                if (option == "EQ")
                {
                    or1.CommandText += " AND (GSCAENDDAT>=" + today + " OR GSCNEUDAT>=" + today + ")";
                }
                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                // GAMANDANT,GAAUFKRS,GAAUFLFD,GAAUFPOS,GASA,GAPOS
                keylist.Add("GSCMANDANT");
                keylist.Add("GSCART");
                keylist.Add("GSCVAR");
                keylist.Add("GSCFARBE");
                doc = DataToXml.Convert2XML(root, Result.Tables[0], key, keylist, true, SaveXML);

                Return.Merge(Result);
            }
            catch (Exception ex)
            {
            }
            return doc;
        }
        public XDocument QueryLIEFDAT(string root, DataRow dwData, ref DataTable tb, string option = "LE", bool SaveXML = false)
        {
            DataSet Return = new DataSet();
            DataSet Result = new DataSet();
            XDocument doc = null;
            DataTable tbLocal = null;
            OracleCommand or1;
            string table = "LIEFDAT";
            try
            {
                //S LSAEND,LSNEUD 
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT  ROWNUM AS MAINKEY,LSMANDANT,LSLIEFNR,LSLIEFNA,LSNAME1,LSNAME2,LSSTRASSE1,LSLAND,LSPLZ,LSORT,LSTELEX,LSMAILADR "
                + ",LSLIEFNA AS PHOTO FROM LIEFDAT WHERE LSMANDANT='00'";
                if (option == "EQ")
                {
                    or1.CommandText += " AND (LSAEND>=" + today + " OR LSNEUD>=" + today + ")";
                }
                Result = Connect2Proxy(or1);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = table;
                    tb = Result.Tables[0].Copy();
                    CompressMainkey(Result.Tables[0]);
                }
                string key = table;
                ArrayList keylist = new ArrayList();
                // GAMANDANT,GAAUFKRS,GAAUFLFD,GAAUFPOS,GASA,GAPOS
                keylist.Add("LSMANDANT");
                keylist.Add("LSLIEFNR");
                doc = DataToXml.Convert2XML("PhoneDB", Result.Tables[0], key, keylist);

                Return.Merge(Result);
            }
            catch (Exception ex)
            {
            }
            return doc;
        }
        #endregion

    }
}